package sy.service.impl;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import sy.dao.RouteDaoI;
import sy.model.Troute;
import sy.pageModel.DataGrid;
import sy.pageModel.PageHelper;
import sy.pageModel.Route;
import sy.service.RouteServiceI;
import sy.util.excel.ExcelWorkSheet;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.*;

@Service
public class RouteServiceImpl implements RouteServiceI {

	@Autowired
	private RouteDaoI routeDao;

	@Override
	public DataGrid dataGrid(Route route, PageHelper ph) {
		DataGrid dg = new DataGrid();
		List<Route> ul = new ArrayList<Route>();
		Map<String, Object> params = new HashMap<String, Object>();
		String hql = " from Troute t ";
		List<Troute> l = routeDao.find(hql + whereHql(route, params) + orderHql(ph), params, ph.getPage(), ph.getRows());
		if (l != null && l.size() > 0) {
			for (Troute t : l) {
				Route u = new Route();
				BeanUtils.copyProperties(t, u);
				ul.add(u);
			}
		}
		dg.setRows(ul);
		dg.setTotal(routeDao.count("select count(*) " + hql + whereHql(route, params), params));
		return dg;
	}

	private String whereHql(Route route, Map<String, Object> params) {
		String hql = "";
		if (route != null) {
			hql += " where 1=1 ";
			if (route.getName() != null) {
				hql += " and t.name like :name";
				params.put("name", "%%" + route.getName() + "%%");
			}
			if (route.getCode() != null) {
				hql += " and t.code like :code";
				params.put("code", "%%" + route.getCode() + "%%");
			}

		}
		return hql;
	}

	private String orderHql(PageHelper ph) {
		String orderString = "";
		if (ph.getSort() != null && ph.getOrder() != null) {
			orderString = " order by t." + ph.getSort() + " " + ph.getOrder();
		}
		return orderString;
	}

	@Override
	synchronized public void add(Route route) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("code", route.getCode());
		if (routeDao.count("select count(*) from Troute t where t.code = :code", params) > 0) {
			throw new Exception("产品编码已存在！");
		} else {
			Troute troute = new Troute();
			BeanUtils.copyProperties(route, troute);
			routeDao.save(troute);
		}
	}

	@Override
	public Route get(String id) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", id);
		Troute t = routeDao.get("select distinct t from Troute t where t.id = :id", params);
		Route route = new Route();
		BeanUtils.copyProperties(t,route);
		return route;
	}

	@Override
	synchronized public void edit(Route route) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", route.getId());
		params.put("code", route.getCode());
		if (routeDao.count("select count(*) from Troute t where t.code = :code and t.id != :id", params) > 0) {
			throw new Exception("产品编码已存在！");
		} else {
			Troute troute = routeDao.get(Troute.class, route.getId());
			BeanUtils.copyProperties(route, troute);
		}
	}

	@Override
	public void delete(String id) {
		routeDao.delete(routeDao.get(Troute.class, id));
	}

	@Override
	public String saveroutewithExcel(MultipartFile excelFile) {
		String message = "导入失败";
		Workbook book = null;
		String filetype=excelFile.getContentType();
		// 判断文件类型
		if (filetype.equals("application/vnd.ms-excel")||filetype.equals("application/ms-excel")) {
			try {
				book = new HSSFWorkbook(excelFile.getInputStream());
			} catch (FileNotFoundException e) {
				message = "导入失败未检索到模版数据";
				e.printStackTrace();
			} catch (IOException e) {
				message = "导入失败";
				e.printStackTrace();
			}
		}else{
			return "文件类型错误！请选择excel导入！";
		}
		// book.getNumberOfSheets(); 判断Excel文件有多少个sheet
		Sheet sheet = book.getSheetAt(0);
		ExcelWorkSheet<Troute> excelWorkSheet = new ExcelWorkSheet<Troute>();
		// 保存工作单名称
		Row firstRow = sheet.getRow(0);
		Iterator<Cell> iterator = firstRow.iterator();
		// 保存列名
		List<String> cellNames = new ArrayList<String>();
		while (iterator.hasNext()) {
			cellNames.add(iterator.next().getStringCellValue());
		}
		excelWorkSheet.setColumns(cellNames);
		int m=0;    //记录导入信息与以存在信息的重复数量，即更新数。
		for (int i = 1; i <= sheet.getLastRowNum(); i++) {
			
			Troute route=null;
			String routeCode="";
			Row ros = sheet.getRow(i);
			//设置产品编码
			if(ros.getCell(0) != null && !ros.getCell(0).toString().trim().equals("")){
				ros.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
				routeCode=ros.getCell(0).getStringCellValue();
			}else{
				return "第"+(i+1)+"行产品编码不能为空";
			}
			route=routeDao.get("from Troute t where t.code='"+routeCode+"'");
			if(route!=null){
				m++;
			}else{
				route = new Troute();
				route.setId(UUID.randomUUID().toString());
				route.setCode(routeCode);
			}
			//设置产品名称
			if(ros.getCell(1) != null&& !ros.getCell(1).toString().trim().equals("")){
				ros.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
				route.setName(ros.getCell(1).getStringCellValue());
			}
			//设置产品描述
			if(ros.getCell(2) != null&& !ros.getCell(2).toString().trim().equals("")){
				ros.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
			}
			//设置产品重量
			if(ros.getCell(3) != null && !ros.getCell(3).toString().trim().equals("")){
				ros.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
				try{
				}catch (Exception e){
					return "第"+(i+1)+"行产品重量错误，请填写数字！";
				}
			}else{
				return "第"+(i+1)+"行产品重量不能为空";
			}
			//设置产品体积
			if(ros.getCell(4) != null && !ros.getCell(4).toString().trim().equals("")){
				ros.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
				try{
				}catch (Exception e){
					return "第"+(i+1)+"行产品体积错误，请填写数字！";
				}
			}else{
				return "第"+(i+1)+"行产品体积不能为空";
			}
			//设置备注
			if(ros.getCell(5) != null && !ros.getCell(5).toString().trim().equals("")){
				ros.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
			}
			routeDao.saveOrUpdate(route);
			int n=sheet.getLastRowNum()-m; //记录添加记录的数量。
			message="导入成功！添加了"+n+"条数据，更新了"+m+"条数据";
		}
		return message;
	}
}
